﻿using AutoMapper;
using InterFace;
using Models.Entity;
using Models.Vo;
using SqlSugar;

namespace Service;

public class NoticeService : INoticeService
{
    private readonly IMapper _mapper;
    private ISqlSugarClient _db { get; set; }

    public NoticeService(ISqlSugarClient db, IMapper mapper)
    {
        _db = db;
        _mapper = mapper;
    }
    public async Task<List<FaultNotice>> GetNotices()
    {
       return await _db.Queryable<FaultNotice>().Where(fn => fn.ExecuteStatus != "关闭") // 排除关闭状态的工单
           .ToListAsync();
    }

    /*public async Task<TicketStatus> GetTicketStatus()
    {
        var result = new TicketStatus();

        var statusCounts = await _db.Queryable<FaultNotice>()
            .GroupBy(fn => fn.ExecuteStatus)
            .Select(g => new 
            {
                Status = g.ExecuteStatus,
                Count = SqlFunc.AggregateCount(g.ID)
            })
            .ToListAsync();

        foreach (var item in statusCounts)
        {
            switch (item.Status)
            {
                case "处理中":
                    result.Processing = item.Count;
                    break;
                case "待分配":
                    result.Allocate = item.Count;
                    break;
                case "已完成":
                    result.Completed = item.Count;
                    break;
                case "待验收":
                    result.Check = item.Count;
                    break;
                case "挂起":
                    result.Hang = item.Count;
                    break;
            }
        }

        result.Total = await _db.Queryable<FaultNotice>().CountAsync();

        return result;
    }*/

    public async Task<TicketStatusVo> GetTicketStatus()
    {
        var result = new TicketStatusVo();

        // 获取当前时间和时间范围
        var now = DateTime.Now;
        var startOfDay = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
        var endOfDay = startOfDay.AddDays(1);
        var startOfMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        var endOfMonth = startOfMonth.AddMonths(1);

        // 查询总数据
        //await FillStatusCounts(result.Total, null, null);

        // 查询本日数据
        await FillStatusCounts(result.Today, startOfDay, endOfDay);

        // 查询本月数据
        await FillStatusCounts(result.Month, startOfMonth, endOfMonth);

        return result;
    }
    
    #region 获取状态数量
    private async Task FillStatusCounts(TicketStatus target, DateTime? startTime = null, DateTime? endTime = null)
    {
        var query = _db.Queryable<FaultNotice>();

        if (startTime.HasValue && endTime.HasValue)
        {
            query = query.Where(fn => fn.CreateTime >= startTime && fn.CreateTime < endTime);
        }

        // 统计各状态数量
        var statusCounts = await query
            .GroupBy(fn => fn.ExecuteStatus)
            .Select(g => new 
            {
                Status = g.ExecuteStatus,
                Count = SqlFunc.AggregateCount(g.ID)
            })
            .ToListAsync();

        // 映射到目标对象
        foreach (var item in statusCounts)
        {
            switch (item.Status)
            {
                case "处理中":
                    target.Processing = item.Count;
                    break;
                case "待分配":
                    target.Allocate = item.Count;
                    break;
                case "关闭":
                    target.Completed = item.Count;
                    break;
                case "待验收":
                    target.Check = item.Count;
                    break;
                case "挂起":
                    target.Hang = item.Count;
                    break;
            }
            // 统计总数
            
        }
        target.Total = target.Allocate+target.Completed+target.Check+target.Hang+target.Processing;
    }
    #endregion
    
    public async Task<List<UserCompletionRateVo>> GetCompletionRate()
    {
        // 获取当前时间及时间范围
        var now = DateTime.Now;
        var startOfMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        var endOfMonth = startOfMonth.AddMonths(1);

        var result=await _db.Queryable<UserInfo, FaultNotice>((u, f) => new object[]
            {
                JoinType.Left, u.ID == f.UserId
            })
            .Where((u, f) => f.CreateTime >= startOfMonth && f.CreateTime < endOfMonth) // 仅筛选本月的工单
            .GroupBy((u, f) => new { u.LastName, u.FirstName })
            .Select((u, f) => new UserCompletionRateVo
            {
                UserName = u.LastName+u.FirstName,
                TotalOrders = SqlFunc.AggregateCount(f.ID),
                CompletedOrders = SqlFunc.AggregateSum(
                    SqlFunc.IIF(f.ExecuteStatus == "关闭", 1, 0)
                )
            })
            .ToListAsync();

        // 处理除零异常
        foreach (var item in result)
        {
            if (item.TotalOrders == 0)
            {
                item.CompletionRate = 0;
            }
            else
            {
                item.CompletionRate = (double)item.CompletedOrders / item.TotalOrders;
            }

            item.Rating = "4.7";
        }

        return result;
    }

    public async Task<List<TicketVo>> GetTickets()
    {
        return await _db.Queryable<FaultNotice, EquipmentData, UserInfo>((fn, eq, u) => new object[]
            {
                JoinType.Inner, fn.EquipmentID == eq.ID, // 设备表连接
                JoinType.Inner, fn.Creator == u.ID       // 用户表连接
            })
            .Where((fn, eq, u) => fn.ExecuteStatus!="关闭") // 按设备 ID 筛选
            .Select((fn, eq, u) => new TicketVo
            {
                EquipmentNumber = eq.EquipmentCode,
                EquipmentName = eq.EquipmentName,
                Creator = u.LastName+u.FirstName, // 将用户 ID 转为用户名
                CreateTime = fn.CreateTime,
                EquipmentStatus = "故障",
                FaultLevel = "一般", // 假设故障等级存储在 FaultDesc
                TicketStatus = fn.ExecuteStatus,
                Duration = SqlFunc.DateDiff(DateType.Hour, fn.CreateTime, SqlFunc.GetDate()).ToString() + " 小时"
            })
            .ToListAsync();
    }

    /*public async Task<List<FaultInfoVo>> GetFaultTypeCounts()
    {
        return await _db.Queryable<FaultCodeInfo, FaultInfo>((fc, fi) => new object[]
            {
                JoinType.Inner, fc.ID == fi.FaultType
            })
            .GroupBy((fc, fi) => fc.FaultType) // 按故障类型分组
            .Select((fc, fi) => new FaultInfoVo()
            {
                FaultType = fc.FaultType,
                Count = SqlFunc.AggregateCount(fc.FaultCode) // 统计每个类型的故障代码数量
            })
            .ToListAsync();
    }*/
    public async Task<FaultTypeCountVo> GetFaultTypeCounts()
    {
        var result = new FaultTypeCountVo();

        // 获取当前时间及时间范围
        var now = DateTime.Now;
        var startOfMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        var endOfMonth = startOfMonth.AddMonths(1);

        var startOfLastMonth = startOfMonth.AddMonths(-1);
        var endOfLastMonth = startOfMonth;

        // 查询总数据
        //result.Total = await GetFaultTypeCountByPeriod(null, null);

        // 查询本月数据
        result.ThisMonth = await GetFaultTypeCountByPeriod(startOfMonth, endOfMonth);

        // 查询上月数据
        result.LastMonth = await GetFaultTypeCountByPeriod(startOfLastMonth, endOfLastMonth);

        return result;
    }

    private async Task<List<FaultTypeCount>> GetFaultTypeCountByPeriod(DateTime? startTime = null, DateTime? endTime = null)
    {
        var query = _db.Queryable<FaultCodeInfo, FaultInfo>((fc, fi) => new object[]
        {
            JoinType.Inner, fc.ID == fi.FaultType
        });

        if (startTime.HasValue && endTime.HasValue)
        {
            query = query.Where((fc, fi) => fi.StartTime >= startTime && fi.StartTime < endTime);
        }

        return await query
            .GroupBy((fc, fi) => fc.FaultType)
            .OrderByDescending((fc, fi) =>SqlFunc.AggregateCount(fc.FaultCode))
            .Select((fc, fi) => new FaultTypeCount
            {
                FaultType = fc.FaultType,
                Count = SqlFunc.AggregateCount(fc.FaultCode) // 统计每个类型的故障记录数
            })
            
            .ToListAsync();
    }
    /*public async Task<TicketTrendVo> GetDailyTicketTrend()
    {
        // 获取当前日期的起始时间（00:00:00）和结束时间（23:59:59）
        var today = DateTime.Today;
        var startOfDay = today;
        var endOfDay = today.AddDays(1).AddTicks(-1);

        // 执行基础查询，获取小时和计数
        var queryResult = await _db.Queryable<FaultNotice>()
            .Where(fn => fn.CreateTime >= startOfDay && fn.CreateTime <= endOfDay)
            // 使用DateDiff进行分组（兼容无DatePart的版本）
            .GroupBy(fn => SqlFunc.DateDiff(DateType.Hour, startOfDay, fn.CreateTime))
            .Select(fn => new 
            {
                Hour = SqlFunc.DateDiff(DateType.Hour, startOfDay, fn.CreateTime),
                Count = SqlFunc.AggregateCount(fn.ID)
            })
            .ToListAsync();

        // 转换为结果对象
        var result = queryResult.Select(item => new TicketTrend
            {
                Time = item.Hour,  // 直接使用小时差作为小时数（0-23）
                Count = item.Count
            })
            .ToList();

        // 补全当天24小时数据（确保每个小时都有记录）
        for (int hour = 0; hour < 24; hour++)
        {
            if (!result.Any(t => t.Time == hour))
            {
                result.Add(new TicketTrend
                {
                    Time = hour,
                    Count = 0
                });
            }
        }

        // 按小时排序后返回
        return result.OrderBy(t => t.Time).ToList();
       
    }*/
    public async Task<TicketTrendVo> GetDailyTicketTrend()
    {
        var result = new TicketTrendVo();

        // 获取当前时间
        var now = DateTime.Now;

        // 今日时间范围：00:00:00 ~ 23:59:59
        var todayStart = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
        var todayEnd = todayStart.AddDays(1).AddTicks(-1);

        // 昨日时间范围：00:00:00 ~ 23:59:59
        var yesterdayStart = todayStart.AddDays(-1);
        var yesterdayEnd = todayStart;

        // 查询今日数据
        result.This = await GetTicketDailyTrendByPeriod(todayStart, todayEnd);

        // 查询昨日数据
        result.Last = await GetTicketDailyTrendByPeriod(yesterdayStart, yesterdayEnd);

        return result;
    }
    private async Task<List<TicketTrend>> GetTicketDailyTrendByPeriod(DateTime startTime , DateTime endTime )
    {
        var query = _db.Queryable<FaultNotice>();
        
        query = query.Where(fn => fn.CreateTime >= startTime && fn.CreateTime < endTime);
        

        // 按小时分组（兼容无 DatePart 的数据库）
        var queryResult = await query
            .GroupBy(fn => SqlFunc.DateDiff(DateType.Hour, startTime , fn.CreateTime))
            .Select(fn => new 
            {
                Hour = SqlFunc.DateDiff(DateType.Hour, startTime , fn.CreateTime),
                Count = SqlFunc.AggregateCount(fn.ID)
            })
            .ToListAsync();

        // 转换为结果对象
        var result = queryResult.Select(item => new TicketTrend
        {
            Time = item.Hour,
            Count = item.Count
        }).ToList();

        // 补全 24 小时数据（确保每个小时都有记录）
        for (int hour = 0; hour < 24; hour++)
        {
            if (!result.Any(t => t.Time == hour))
            {
                result.Add(new TicketTrend
                {
                    Time = hour,
                    Count = 0
                });
            }
        }

        return result.OrderBy(t => t.Time).ToList();
    }
    public async Task<TicketTrendVo> GetMonthlyTicketTrend()
    {
        var result = new TicketTrendVo();

        // 获取当前时间和时间范围
        var now = DateTime.Now;
        var startOfMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        var endOfMonth = startOfMonth.AddMonths(1);

        var startOfLastMonth = startOfMonth.AddMonths(-1);
        var endOfLastMonth = startOfMonth;

        // 查询总数据
        //result.Total = await GetTicketTrendByPeriod<DailyTicketTrend>(null, null);

        // 查询本月数据
        result.This = await GetTicketMonthlyTrendByPeriod(startOfMonth, endOfMonth);

        // 查询上月数据
        result.Last = await GetTicketMonthlyTrendByPeriod(startOfLastMonth, endOfLastMonth);

        return result;
    }
    private async Task<List<TicketTrend>> GetTicketMonthlyTrendByPeriod(DateTime startTime , DateTime endTime )
    {
        var query = _db.Queryable<FaultNotice>();

       
        query = query.Where(fn => fn.CreateTime >= startTime && fn.CreateTime < endTime);
        

        // 按天分组（兼容无 DatePart 的数据库）
        var queryResult = await query
            .GroupBy(fn => SqlFunc.DateDiff(DateType.Day, startTime , fn.CreateTime))
            .Select(fn => new 
            {
                Day = SqlFunc.DateDiff(DateType.Day, startTime , fn.CreateTime),
                Count = SqlFunc.AggregateCount(fn.ID)
            })
            .ToListAsync();

        // 转换为结果对象
        var result = queryResult.Select(item => new TicketTrend
        {
            Time = item.Day + 1,
            Count = item.Count
        }).ToList();

        // 补全当月/上月的天数（确保每天都有记录）
        int daysInMonth;
        
            var month = startTime.Month;
            var year = startTime.Year;
            daysInMonth = DateTime.DaysInMonth(year, month);
        

        for (int day = 1; day <= daysInMonth; day++)
        {
            if (!result.Any(t => t.Time == day))
            {
                result.Add(new TicketTrend
                {
                    Time = day,
                    Count = 0
                });
            }
        }

        return result.OrderBy(t => t.Time).ToList();
    }
    /*public async Task<List<TicketTrend>> GetMonthlyTicketTrend()
    {
        // 获取当前月份的起始时间（1 日 00:00:00）和结束时间（当月最后一天 23:59:59）
        var today = DateTime.Today;
        var startOfMonth = new DateTime(today.Year, today.Month, 1);
        var endOfMonth = startOfMonth.AddMonths(1).AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(59);
        // 获取当月的总天数
        int daysInMonth = DateTime.DaysInMonth(today.Year,today.Month);
        // 执行查询，按天分组统计
        var queryResult = await _db.Queryable<FaultNotice>()
            .Where(fn => fn.CreateTime >= startOfMonth && fn.CreateTime <= endOfMonth)
            // 计算与当月第一天的天数差，作为日期（1-31）
            .GroupBy(fn => SqlFunc.DateDiff(DateType.Day, startOfMonth, fn.CreateTime) + 1)
            .Select(fn => new 
            {
                Day = SqlFunc.DateDiff(DateType.Day, startOfMonth, fn.CreateTime) + 1,
                Count = SqlFunc.AggregateCount(fn.ID)
            })
            .ToListAsync();

        // 转换为结果对象
        var result = queryResult.Select(item => new TicketTrend
            {
                Time = item.Day,  // 1-31的日期
                Count = item.Count
            })
            .ToList();

        // 补全当月所有天数的数据（确保1-当月最大天数都有记录）
        for (int day = 1; day <= daysInMonth; day++)
        {
            if (!result.Any(t => t.Time == day))
            {
                result.Add(new TicketTrend
                {
                    Time = day,
                    Count = 0
                });
            }
        }

        // 按日期排序后返回
        return result.OrderBy(t => t.Time).ToList();
    }*/

    /*public async Task<List<EquipmentFault>> GetAllDeviceFaultRanking()
    {
        return await _db.Queryable<FaultNotice>()
            .LeftJoin<EquipmentData>((fn, e) => fn.EquipmentID == e.ID)
            .GroupBy((fn, e) => new { fn.EquipmentID,e.EquipmentName })
            .OrderByDescending((fn, e) =>SqlFunc.AggregateCount(fn.ID))
            .Select((fn, e) => new EquipmentFault()
            {
                EquipmentId =fn.EquipmentID,
                EquipmentName = e.EquipmentName ,
                FaultCount = SqlFunc.AggregateCount(fn.ID) // 使用主键 ID 统计更准确
            })
            .ToListAsync();
    }*/
    public async Task<DeviceFaultRankingVo> GetAllDeviceFaultRanking()
    {
        var result = new DeviceFaultRankingVo();

        // 获取当前时间和时间范围
        var now = DateTime.Now;
        var startOfMonth = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
        var endOfMonth = startOfMonth.AddMonths(1);

        var startOfLastMonth = startOfMonth.AddMonths(-1);
        var endOfLastMonth = startOfMonth;

        // 查询总数据
        //result.Total = await GetDeviceFaultRankingByPeriod(null, null);

        // 查询本月数据
        result.ThisMonth = await GetDeviceFaultRankingByPeriod(startOfMonth, endOfMonth);

        // 查询上月数据
        result.LastMonth = await GetDeviceFaultRankingByPeriod(startOfLastMonth, endOfLastMonth);

        return result;
    }

    private async Task<List<EquipmentFault>> GetDeviceFaultRankingByPeriod(DateTime? startTime = null, DateTime? endTime = null)
    {
        var query = _db.Queryable<FaultNotice>()
            .LeftJoin<EquipmentData>((fn, e) => fn.EquipmentID == e.ID);

        if (startTime.HasValue && endTime.HasValue)
        {
            query = query.Where((fn, e) => fn.CreateTime >= startTime && fn.CreateTime < endTime);
        }

        return await query
            .GroupBy((fn, e) => new { fn.EquipmentID, e.EquipmentName })
            .OrderByDescending((fn, e) => SqlFunc.AggregateCount(fn.ID))
            .Select((fn, e) => new EquipmentFault
            {
                EquipmentId = fn.EquipmentID,
                EquipmentName = e.EquipmentName , 
                FaultCount = SqlFunc.AggregateCount(fn.ID) // 使用主键 ID 统计更准确
            })
            .ToListAsync();
    }
}